import pandas as pd

df = pd.read_csv("lending.dat", sep=',', header=0, names=['uid', 'bid', 'title', 'date'])
# 1) 查询每一本书被多少人借过
def count_borrowers_per_book():
    borrowers_per_book = df.groupby('bid')['uid'].nunique().reset_index()
    return borrowers_per_book

# 2) 查询被借次数最多的30本书
def top_30_books_by_borrow_count():
    book_counts = df['bid'].value_counts().reset_index()
    book_counts.columns = ['bid', 'count']
    top_30_books = book_counts.head(30)
    return top_30_books

# 3) 查询每个用户最晚借书时间
def latest_borrow_date_per_user():
    latest_borrow_date = df.groupby('uid')['date'].max().reset_index()
    return latest_borrow_date

# 4) 查询每本书每年被多少人借过（从多到少排序）
def borrow_count_per_year_by_book():
    df['year'] = df['date'].str.split('-').str[0]
    borrow_count_per_year = df.groupby(['bid', 'year'])['uid'].size().reset_index(name='count')
    sorted_result = borrow_count_per_year.sort_values(by=['bid', 'count'], ascending=[True, False])
    return sorted_result

# 5) 查询平均年借书量最少的100位用户
def bottom_100_users_by_avg_borrow_count():
    df['year'] = df['date'].str.split('-').str[0]
    avg_borrow_count_per_user = df.groupby('uid')['bid'].nunique().reset_index(name='count')
    bottom_100_users = avg_borrow_count_per_user.nsmallest(100, 'count')
    return bottom_100_users

# 6) 查询每个用户借了多少书（从多到少排序）
def borrow_count_per_user():
    borrow_count_per_user = df.groupby('uid')['bid'].nunique().reset_index(name='count')
    sorted_result = borrow_count_per_user.sort_values(by='count', ascending=False)
    return sorted_result

# 7) 查询哪10本书在一年内被借次数最多
def top_10_books_in_year():
    df['year'] = df['date'].str.split('-').str[0]
    book_counts_for_year = df.groupby(['bid', 'year'])['uid'].size().reset_index(name='count')
    top_10_books = book_counts_for_year.groupby('year').apply(lambda x: x.nlargest(10, 'count')).reset_index(drop=True)
    return top_10_books

# 8) 查询每个用户最早哪一年开始借书
def earliest_borrow_year_per_user():
    df['year'] = df['date'].str.split('-').str[0]
    earliest_borrow_year = df.groupby('uid')['year'].min().reset_index(name='earliest_year')
    return earliest_borrow_year

# 9) 查询每个用户每年的借书量
def borrow_count_per_user_per_year():
    df['year'] = df['date'].str.split('-').str[0]
    borrow_count_per_user_per_year = df.groupby(['uid', 'year'])['bid'].nunique().reset_index(name='count')
    return borrow_count_per_user_per_year

print(count_borrowers_per_book())
print(top_30_books_by_borrow_count())
print(latest_borrow_date_per_user())
print(borrow_count_per_year_by_book())
print(bottom_100_users_by_avg_borrow_count())
print(borrow_count_per_user())
print(top_10_books_in_year())
print(earliest_borrow_year_per_user())
print(borrow_count_per_user_per_year())
